use std::borrow::Cow;
use std::collections::HashMap;
use std::fs;

use crate::config::{get_rept_config, DataSourceType, GroupCalc, Report};
use crate::database::{
    get_leaf_columns, get_low_cardinality_col_options, parse_order, DrillDownRequ, SqlExecutor,
    TableReader,
};
use crate::error::{Error, Result};
use crate::export::{export_muliti_xlsx_zip, export_xlsx};
use crate::{Column, LowCardinality, to_context_menus};
use async_trait::async_trait;
use serde_json::Value;
use shq_common::prelude::report::{AuthFilter, ReportDefine, ReportGroup, Unit};
use shq_common::prelude::{
    get_work_path, ColumnCell, ColumnCells, DataType, DbType, DefaultValue, Parameter, RowRange,
};
use shq_common::prelude::{report::DisplayCols, OrderItems, WhereCauses};
pub struct ListReport<'a> {
    pub rept_name: &'a str,
    pub rept_conf: &'a Report,
    pub(super) user_id: u32,
    pub(super) menu_id: u32,
    pub(super) params: Vec<(&'a str, String)>,
    pub(super) tmp_table_name: Option<String>,
}
impl<'a> ListReport<'a> {
    pub async fn from(
        rept_name: &'a str,
        params: Vec<(&'a str, String)>,
        user_id: u32,
        menu_id: u32,
    ) -> Result<Self> {
        let rept_conf =
            get_rept_config(rept_name).ok_or(Error::from_create(rept_name, "报表不存在"))?;
        let mut lr = ListReport {
            rept_name,
            rept_conf,
            params,
            user_id,
            menu_id,
            tmp_table_name: None,
        };
        lr.create().await?;
        Ok(lr)
    }
    async fn create(&mut self) -> Result<()> {
        let leaf_cols = self.rept_conf.get_leaf_columns();
        let params = self
            .params
            .iter()
            .map(|p| (p.0, p.1.as_str()))
            .collect::<Vec<_>>();
        let se = SqlExecutor::new(&self.rept_name, params.as_slice());
        for ref_rept in self.rept_conf.data_source.ref_repts.iter() {
            let mut ref_params: Vec<(&str, String)> = Vec::new();
            let ref_rept_conf = get_rept_config(ref_rept.name.as_str())
                .ok_or(anyhow::anyhow!("报表{}不存在", ref_rept.name))?;
            for p in ref_rept_conf.params.iter() {
                let pval=if let Some(v) = ref_rept.param_map.get(&p.name) {
                    if let DefaultValue::Str(sv) = v {
                        if sv.starts_with("@") {
                            let param_name = &sv[1..];
                            self.params
                                .iter()
                                .find(|(pk, _)| pk == &param_name)
                                .map(|(_, pv)| pv.as_str())
                                .unwrap_or("")
                                .to_owned()
                        } else {
                            sv.to_owned()
                        }
                    } else {
                        v.to_string()
                    }
                }else{
                    p.default_value.to_string()
                };
                ref_params.push((p.name.as_str(), pval));
            }
            let mut lr = Box::pin(ListReport::from(
                ref_rept.name.as_str(),
                ref_params,
                self.user_id,
                self.menu_id,
            ))
            .await?;
            Box::pin(lr.create()).await?;
        }
        let tname = match self.rept_conf.data_source.source_type {
            DataSourceType::Table => {
                let sql_str = if let Some(wc) = &self.rept_conf.data_source.where_cause {
                    format!(
                        "select * from {} where {}",
                        self.rept_conf.data_source.content, wc
                    )
                } else {
                    self.rept_conf.data_source.content.to_owned()
                };
                se.exec(
                    &sql_str,
                    &leaf_cols,
                    &self.rept_conf.params,
                    &self.rept_conf.groups,
                    &self.rept_conf.unique_columns,
                )
                .await?
            }
            DataSourceType::Sql => {
                se.exec(
                    &self.rept_conf.data_source.content,
                    &leaf_cols,
                    &self.rept_conf.params,
                    &self.rept_conf.groups,
                    &self.rept_conf.unique_columns,
                )
                .await?
            }
            DataSourceType::SqlFile => {
                let file_path = format!(
                    "{}/sqls/{}",
                    get_work_path(),
                    self.rept_conf.data_source.content
                );
                let sql_str = fs::read_to_string(&file_path).or_else(|err| {
                    Err(Error::from_read(
                        self.rept_name,
                        format!("读取SQL文件{file_path}失败: {}", err),
                    ))
                })?;
                se.exec(
                    sql_str.as_str(),
                    &leaf_cols,
                    &self.rept_conf.params,
                    &self.rept_conf.groups,
                    &self.rept_conf.unique_columns,
                )
                .await?
            }
            DataSourceType::Func => {
                let func = self.rept_conf.data_source.content.as_str();
                se.exec_func(
                    func,
                    &leaf_cols,
                    &self.rept_conf.groups,
                    &self.rept_conf.unit,
                )
                .await?
            }
        };
        self.tmp_table_name = Some(tname.to_string());
        Ok(())
    }
    async fn load_data_list(
        &self,
        where_causes: Option<WhereCauses>,
        orders: Option<OrderItems>,
        display_cols: DisplayCols,
        group_index: u8,
        drill_downs: Vec<(u8, Value)>,
        extra_where: Option<String>,
        page: u32,
        size: u16,
        has_front_must: bool,
    ) -> Result<shq_common::prelude::report::ReportDataResponse> {
        let row_range = shq_auth::get_row_range(self.user_id, self.menu_id).await?;
        if group_index != 0 && group_index >= self.rept_conf.groups.len() as u8 {
            return Err(Error::from_read(self.rept_name, "分组索引group_index越界"));
        }
        let leaf_cols = get_leaf_columns(&self.rept_conf.columns);
        let where_cause = parse_where(
            self.rept_conf.auth_filter.as_ref(),
            leaf_cols.as_slice(),
            self.rept_conf.groups.as_slice(),
            where_causes,
            row_range,
            extra_where,
            self.user_id,
            group_index,
            &self.rept_conf.unit,
        );
        let has_subtotal = self.rept_conf.groups.iter().any(|g| g.subtotal.is_some());
        let def_order: Cow<str> = if !has_subtotal && !self.rept_conf.groups.is_empty() {
            let idx = group_index as usize;
            let v = self.rept_conf.groups[idx].by.as_str();
            Cow::Borrowed(v)
        } else {
            let v = self.rept_conf.unique_columns.join(",");
            if let Some(dord) = self.rept_conf.default_order.as_deref() {
                Cow::Owned(format!("{dord},{v}"))
            } else {
                Cow::Owned(v)
            }
        };
        let order_by = parse_order(orders.as_ref(), def_order.as_ref());
        let columns = get_display_cols(
            leaf_cols.as_slice(),
            self.rept_conf.groups.as_slice(),
            &display_cols,
            group_index,
            has_front_must,
        );
        if self.tmp_table_name.is_none() {
            return Err(Error::from_conf(self.rept_name, "报表未创建"));
        }
        if columns.len() == 0 {
            return Err(Error::from_conf(self.rept_name, "没有可显示的列"));
        }
        let tr = TableReader::new(
            self.tmp_table_name.as_ref().unwrap().as_str(),
            &columns,
            self.rept_conf.unit.clone(),
        );
        if page < 1 {
            return Err(Error::from_conf(self.rept_name, "page必须从1开始"));
        }
        let offset = (page - 1) as u64 * size as u64;
        let mut res = tr
            .get_page_data(where_cause.as_str(), order_by.as_str(), size, offset)
            .await?;
        let drill_downs = drill_downs
            .into_iter()
            .filter(|(g, _)| *g == group_index)
            .collect::<Vec<_>>();
        let requs = DrillDownRequ::from_vec(drill_downs, &self.rept_conf.groups);
        let dds = tr.drill_down(requs, orders.as_ref()).await?;
        for dd in dds {
            let gidx = dd.group_index as usize;
            let by_val = dd.by_value;
            let col_name = self.rept_conf.groups[gidx].by.as_str();
            let idx = columns
                .iter()
                .position(|c| c.name.as_deref() == Some(col_name));
            let pos = idx
                .map(|i| res.data.iter().position(|r| r[i] == by_val))
                .flatten();
            if let Some(pos) = pos {
                res.add_drill_down(pos, dd.data);
            }
        }
        Ok(res)
    }
}
#[async_trait]
impl<'a> super::ReportTrait for ListReport<'a> {
    async fn get_rept_define(&self) -> Result<ReportDefine> {
        let row_range = shq_auth::get_row_range(self.user_id, self.menu_id).await?;
        let has_group_subtotal = self.rept_conf.groups.iter().any(|g| g.subtotal.is_some());
        let groups: Vec<ReportGroup> = if has_group_subtotal {
            vec![]
        } else {
            self.rept_conf
                .groups
                .iter()
                .filter(|g| g.row_range <= row_range)
                .cloned()
                .collect()
        };
        let param_vals = self
            .params
            .iter()
            .map(|p| (p.0, p.1.as_str()))
            .collect::<Vec<_>>();
        let mut columns: Vec<ColumnCell> = Vec::new();
        let dds = self.rept_conf.drill_downs.as_slice();
        for c in self.rept_conf.columns.iter() {
            if !c.popedom_types.is_empty() {
                let mut show_col = false;
                for pt in c.popedom_types.iter() {
                    if shq_auth::has_column_range(self.user_id, self.menu_id, pt).await? {
                        show_col = true;
                        break;
                    }
                }
                if !show_col {
                    continue;
                }
            }
            let cell = c.to_column_cell(dds, param_vals.as_slice()).await?;
            columns.push(cell);
        }
        let mut params: Vec<shq_common::prelude::Parameter> = Vec::new();
        for p in self.rept_conf.params.iter() {
            let mut np = p.clone();
            np.default_value.convert_to_str().await?;
            params.push(np);
        }
        let rdr = ReportDefine {
            title: self.rept_conf.title.clone(),
            unique_columns: self.rept_conf.unique_columns.clone(),
            unit: self.rept_conf.unit.clone(),
            params,
            groups,
            columns,
            chart: self.rept_conf.chart.clone(),
            context_menus: to_context_menus(self.rept_conf.drill_downs.as_slice()),
            special_rows: self.rept_conf.special_rows.clone(),
            series_source: self.rept_conf.series_source.clone(),
            has_export_sheet_col: self.rept_conf.export_sheet_col_name.is_some(),
        };
        Ok(rdr)
    }
    async fn get_options_map(&self) -> Result<HashMap<String, Vec<String>>> {
        let mut options_map: HashMap<String, Vec<String>> = HashMap::new();
        if self.tmp_table_name.is_none() {
            return Err(Error::from_conf(self.rept_name, "报表未创建"));
        }
        let leaf_cols = get_leaf_columns(&self.rept_conf.columns);
        for col in leaf_cols.iter() {
            if col.low_cardinality.is_some() {
                if let Some(name) = col.name.as_deref() {
                    let low_cardinality = col.low_cardinality.as_ref().unwrap();
                    if let Some(options) = get_low_cardinality_col_options(
                        self.tmp_table_name.as_ref().unwrap(),
                        name,
                        low_cardinality,
                    )
                    .await
                    .ok()
                    {
                        options_map.insert(name.to_string(), options);
                    }
                }
            }
        }
        Ok(options_map)
    }
    async fn get_data_list(
        &self,
        where_causes: Option<WhereCauses>,
        orders: Option<OrderItems>,
        display_cols: DisplayCols,
        group_index: u8,
        drill_downs: Vec<(u8, Value)>,
        extra_where: Option<String>,
        page: u32,
        size: u16,
    ) -> Result<shq_common::prelude::report::ReportDataResponse> {
        self.load_data_list(
            where_causes,
            orders,
            display_cols,
            group_index,
            drill_downs,
            extra_where,
            page,
            size,
            true,
        )
        .await
    }
    async fn export_xslx(
        &self,
        where_causes: Option<WhereCauses>,
        orders: Option<OrderItems>,
        display_cols: DisplayCols,
        group_index: u8,
        drill_downs: Vec<(u8, Value)>,
        extra_where: Option<String>,
        one_sheet_on_file: bool,
    ) -> Result<Vec<u8>> {
        let leaf_cols = get_leaf_columns(&self.rept_conf.columns);
        let leaf_display_cols = get_display_cols(
            leaf_cols.as_slice(),
            self.rept_conf.groups.as_slice(),
            &display_cols,
            group_index,
            true,
        );
        let dis_cols = leaf_display_cols
            .iter()
            .map(|c| c.name.as_deref().unwrap())
            .collect::<Vec<_>>();
        let params = self
            .params
            .iter()
            .map(|p| (p.0, p.1.as_str()))
            .collect::<Vec<_>>();
        let data = self
            .load_data_list(
                where_causes,
                orders,
                display_cols,
                group_index,
                drill_downs,
                extra_where,
                1,
                0,
                true,
            )
            .await?;
        let title = &self.rept_conf.title.as_str();
        let special_rows = self.rept_conf.special_rows.as_ref();
        let conf_params = &self.rept_conf.params;
        let params = params.as_slice();
        let datas = data.data;
        let sheet_col_name = self.rept_conf.export_sheet_col_name.as_deref();
        let mut cols: Vec<ColumnCell> = Vec::new();
        for col in self.rept_conf.columns.iter() {
            let cell = col.to_column_cell(&[], params).await?;
            cols.push(cell);
        }
        let header_cells = ColumnCells::from_columncells(cols, &dis_cols);
        let datas = if one_sheet_on_file && sheet_col_name.is_some() {
            let sheet_col_name = sheet_col_name.unwrap();
            export_muliti_xlsx_zip(
                title,
                header_cells,
                conf_params,
                params,
                datas,
                sheet_col_name,
                special_rows,
            )
            .await?
        } else {
            export_xlsx(
                title,
                header_cells,
                conf_params,
                params,
                datas,
                sheet_col_name,
                special_rows,
            )
            .await?
        };
        Ok(datas)
    }
}

pub(super) fn parse_where(
    auth_filter: Option<&AuthFilter>,
    leaf_cols: &[&Column],
    groups: &[ReportGroup],
    where_causes: Option<WhereCauses>,
    range: RowRange,
    extra_where: Option<String>,
    user_id: u32,
    group_index: u8,
    rept_unit: &Unit,
) -> String {
    let range_where = if let Some(af) = auth_filter {
        af.to_where_cause(range, user_id)
    } else {
        "".to_string()
    };
    let cols = leaf_cols
        .iter()
        .map(|c| {
            let unit = if let Some(unit) = c.unit.as_ref() {
                unit
            } else {
                rept_unit
            };
            (c.name.as_deref().unwrap(), c.data_type, unit, false)
        })
        .collect::<Vec<_>>();
    let mut wc_str = if let Some(wc) = where_causes {
        let w = wc.to_where_string(DbType::Cilckhouse, &cols);
        w
    } else {
        "".to_string()
    };
    if !range_where.is_empty() {
        if !wc_str.is_empty() {
            wc_str.push_str(" and ");
        }
        wc_str.push_str(&range_where);
    }
    let glen = groups.len();
    let has_subtotal = groups.iter().any(|g| g.subtotal.is_some());
    if glen > 0 && !has_subtotal {
        if group_index < glen as u8 {
            if !wc_str.is_empty() {
                wc_str.push_str(" and ");
            }
            let gi = format!("gp_index={group_index}");
            wc_str.push_str(&gi);
        }
    }
    wc_str = extra_where
        .map(|s| {
            if wc_str.is_empty() {
                s
            } else {
                format!("{} and {}", wc_str, s)
            }
        })
        .unwrap_or_else(|| wc_str);
    if wc_str.is_empty() {
        wc_str
    } else {
        format!("where {}", wc_str)
    }
}
pub(crate) fn get_display_cols<'a>(
    leaf_cols: &'a [&'a Column],
    groups: &[ReportGroup],
    display_cols: &DisplayCols,
    group_index: u8,
    has_front_must: bool,
) -> Vec<&'a Column> {
    let mut cols: Vec<&'a Column> = Vec::new();
    let ginx = group_index as usize;
    let glen = groups.len();
    let mut exclude_cols: Vec<&str> = Vec::new();
    let has_subtotal = groups.iter().any(|g| g.subtotal.is_some());
    if !has_subtotal {
        for i in ginx + 1..glen {
            let g = &groups[i];
            for nc in g.name_cols.iter() {
                exclude_cols.push(nc.as_str());
            }
        }
    }
    match display_cols {
        DisplayCols::Default => {
            for c in leaf_cols.iter() {
                if exclude_cols
                    .iter()
                    .any(|c2| *c2 == c.name.as_deref().unwrap())
                {
                    continue;
                }
                let is_display = c.display || (has_front_must && c.front_must);
                if is_display {
                    cols.push(c);
                }
            }
        }
        DisplayCols::Part(cnames) => {
            for c in leaf_cols.iter() {
                if c.front_must && !has_front_must {
                    continue;
                }
                let cname = c.name.as_deref().unwrap();
                if exclude_cols.iter().any(|c2| *c2 == cname) {
                    continue;
                }
                if cnames.iter().any(|c2| *c2 == cname) {
                    cols.push(c);
                }
            }
        }
    }
    cols
}
pub(crate) fn get_col_name_exps<'a>(
    leaf_cols: &'a [&'a Column],
    group_bys: &'a [&'a str],
    conf_params: &'a [Parameter],
    params: &'a [(&str, &str)],
) -> Vec<(&'a str, Cow<'a, str>, bool)> {
    let res = leaf_cols
        .iter()
        .map(|c| {
            let col_name = c.name.as_deref().unwrap();
            let col_exp = c.col_exp.as_ref().map(|e| e.as_str()).unwrap_or(col_name);
            let mut same_name = col_name == col_exp;
            let col_exp = if !group_bys.is_empty() {
                let is_gb = group_bys.contains(&col_name);
                if is_gb {
                    same_name = false;
                    Cow::Borrowed(col_exp)
                } else if c.group_calc == GroupCalc::Acbl {
                    let dval = conf_params
                        .iter()
                        .filter(|p| p.data_type == DataType::Date)
                        .last()
                        .map(|p| params.iter().find(|(k, _)| *k == p.name).map(|p| p.1))
                        .flatten();
                    if let Some(dval) = dval {
                        Cow::Owned(format!("sumIf({col_exp},DTID=toDate('{dval}'))"))
                    } else {
                        Cow::Owned(format!("anyLast({col_exp})"))
                    }
                } else if c.group_calc == GroupCalc::Avg {
                    let count = conf_params
                        .iter()
                        .filter(|p| p.data_type == DataType::Date)
                        .count();
                    if count < 2 {
                        Cow::Owned(format!("avg({col_exp})"))
                    } else {
                        Cow::Owned(format!("sum({col_exp})/_DAYS"))
                    }
                } else if is_aggregate_exp(col_exp, &c.group_calc) {
                    same_name = false;
                    Cow::Borrowed(col_exp)
                } else {
                    if c.group_calc == GroupCalc::Any {
                        same_name = false;
                    }
                    Cow::Owned(format!("{}({col_exp})", c.group_calc))
                }
            } else {
                same_name = false;
                Cow::Borrowed(col_exp)
            };
            (col_name, col_exp, same_name)
        })
        .collect::<Vec<_>>();
    res
}
fn is_aggregate_exp(exp: &str, group_calc: &GroupCalc) -> bool {
    if *group_calc == GroupCalc::None {
        return true;
    }
    exp.contains('(')
        && (exp.starts_with("dict")
            || exp.starts_with("join")
            || exp.contains("sum")
            || exp.contains("avg")
            || exp.contains("max")
            || exp.contains("min")
            || exp.contains("count")
            || exp.contains("any"))
}

pub(super) async fn to_header_cells(
    columns: &[&Column],
    display_cols: &Vec<&str>,
    params: &[(&str, &str)],
) -> anyhow::Result<ColumnCells> {
    let mut cols: Vec<ColumnCell> = Vec::new();
    for col in columns.iter() {
        let cell = col.to_column_cell(&[], params).await?;
        cols.push(cell);
    }
    let header_cells = ColumnCells::from_columncells(cols, display_cols);
    Ok(header_cells)
}
